package com.plian.system.service.cqzc.bondsapply;

import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.plian.Tools.*;
import com.plian.Tools.DateUtil;
import com.plian.Tools.pagedata.PageData;
import com.plian.system.cache.UserCache;
import com.plian.system.config.exception.MyRuntimeException;
import com.plian.system.constant.sys.FormTypeConstant;
import com.plian.system.constant.yth.YthConstant;
import com.plian.system.dao.bt.baseinfo.BaseInfoDao;
import com.plian.system.dao.cqzc.bondissue.bondIssueDao;
import com.plian.system.dao.cqzc.bondsapply.BondsApplyDao;
import com.plian.system.dao.cqzc.bondsrepayment.BondsRepaymentDao;
import com.plian.system.dao.sys.orgzation.OrgzationDao;
import com.plian.system.entity.fr.bonds.BondsApply;
import com.plian.system.entity.sys.ApprovalMessage;
import com.plian.system.entity.sys.Org;
import com.plian.system.mapper.fr.bonds.BondsApplyMapper;
import com.plian.system.model.CommonCode;
import com.plian.system.model.ListResult;
import com.plian.system.model.ObjResult;
import com.plian.system.model.page.Page;
import com.plian.system.mybatis.base.BaseFlowService;
import com.plian.system.service.sys.code.IFormCodeService;
import com.plian.system.service.sys.file.FileService;
import com.plian.system.service.sys.message.IApprovalMessageService;
import com.plian.system.service.sys.orgzation.IOrgService;
import com.plian.system.service.wf.FormWorkflowService;
import com.plian.system.vo.yth.YthResult;
import org.apache.commons.lang3.exception.ExceptionUtils;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.function.Function;
import java.util.stream.Collectors;
import java.util.stream.Stream;

import static com.plian.system.constant.sys.FormStatusConstant.*;
import static com.plian.system.constant.sys.FormTypeConstant.CQZC_BONDSAPPLY;


/**
 * 说明：发债申请
 */
@Service
public class BondsApplyService extends ServiceImpl<BondsApplyMapper, BondsApply> implements BaseFlowService {
    @Autowired
    BondsApplyDao bondsapplyDao;
    @Autowired
    FileService fileService;
    @Autowired
    bondIssueDao bondIssueDao;
    @Autowired
    BondsRepaymentDao bondsRepaymentDao;
    @Autowired
    OrgzationDao orgzationDao;
    @Autowired
    FormWorkflowService formWorkflowService;
    @Autowired
    IFormCodeService formCodeService;
    @Autowired
    BaseInfoDao baseInfoDao;
    @Autowired
    IOrgService orgService;

    @Autowired
    private IApprovalMessageService approvalMessageService;

    /**
     * logger属性.
     */
    private static final Logger LOGGER = LoggerFactory.getLogger(BondsApplyService.class);
    /**
     * 保存
     *
     * @param pd
     * @return
     */
    public ObjResult save(PageData pd) {
        try {
            String id = UuidUtil.get32UUID();
            pd.put("id", id);
            pd.put("status", "0");
            pd.put("orgId", TokenUtil.getTokenOrgId());
            pd.put("createDate", new Date());
            pd.put("formType", CQZC_BONDSAPPLY);
            pd.put("code", formCodeService.getCode(CQZC_BONDSAPPLY));
            pd.put("createUser", TokenUtil.getTokenUserId());
            pd.put("updateUser", TokenUtil.getTokenUserId());
            pd.put("updateTime", new Date());
            bondsapplyDao.save(pd);
            //保存附件
            fileService.saveAll(id, (List<HashMap>) pd.get("list_doc"));
            HashMap<String, Object> processVariable = new HashMap<>();
            formWorkflowService.startProcessAndSimulate(id, pd.getString("formType"), processVariable);
            ObjResult objResult = new ObjResult(CommonCode.SUCCESS);
            return objResult;
        } catch (Exception e) {
            LOGGER.error(ExceptionUtils.getStackTrace(e));
            return new ObjResult(CommonCode.ERROR);
        }
    }

    /**
     * 修改
     *
     * @param pd
     * @return
     */
    public ObjResult edit(PageData pd) {
        try {
            String id = pd.getString("id");
            pd.put("updateUser", TokenUtil.getTokenUserId());
            pd.put("updateTime", new Date());
            bondsapplyDao.edit(pd);
            //修改附件
            fileService.edit(id, (List<HashMap>) pd.get("list_doc"));
            ObjResult objResult = new ObjResult(CommonCode.SUCCESS);
            return objResult;
        } catch (Exception e) {
            LOGGER.error(ExceptionUtils.getStackTrace(e));
            return new ObjResult(CommonCode.ERROR);
        }
    }

    /**
     * 查询列表
     *
     * @return
     */
    public ListResult listPage(PageData pd) {
        ListResult listResult;
        try {
            pd.put("orgId", TokenUtil.getTokenOrgId());
            boolean isPage = true;
            if (pd.containsKey("isPage")) {
                isPage = (boolean) pd.get("isPage");
            }
            Page page = new Page(isPage, pd);
            List<PageData> list = bondsapplyDao.listPage(page);
            listResult = new ListResult(CommonCode.SUCCESS);
            listResult.setList(list);
            listResult.setTotal(page.getTotal());
        } catch (Exception e) {
            LOGGER.error(ExceptionUtils.getStackTrace(e));
            listResult = new ListResult(CommonCode.ERROR);
        }
        return listResult;
    }

    /**
     * 根据id查询
     *
     * @param pd
     * @return
     */
    public ObjResult findbyid(PageData pd) {
        ObjResult objResult;
        String id = pd.getString("id");
        try {
            PageData p = bondsapplyDao.findbyid(id);
            objResult = new ObjResult(CommonCode.SUCCESS);
            objResult.setPageData(p);
            return objResult;
        } catch (Exception e) {
            LOGGER.error(ExceptionUtils.getStackTrace(e));
            objResult = new ObjResult(CommonCode.ERROR);
            return objResult;
        }
    }

    public ObjResult del_ids(List<String> ids) {
        try {
            ObjResult objResult = new ObjResult(CommonCode.SUCCESS);
            bondsapplyDao.del_ids(ids);
            for (String s : ids) {
                formWorkflowService.deleteProcessInstance(s);
            }
            return objResult;
        } catch (Exception e) {
            LOGGER.error(ExceptionUtils.getStackTrace(e));
            return new ObjResult(CommonCode.ERROR);
        }
    }

    /**
     * 提交单据
     *
     * @param formId 单据id
     */
    public HashMap<String, Object> commit(String formId){
        PageData pageData = new PageData();
        pageData.put("status", String.valueOf(FLOW));
        ArrayList<String> ids = new ArrayList<>();
        ids.add(formId);
        pageData.put("id", ids);
        try {
            bondsapplyDao.refer(pageData);
        } catch (Exception e) {
            log.error(ExceptionUtils.getStackTrace(e));
            throw new MyRuntimeException("提交失败");
        }
        //将需要提交的流程变量返回，用于工作流侧
        return new HashMap<String, Object>();
    }

    /**
     * 退回单据
     *
     * @param formId 单据id
     */
    public Boolean reject(String formId, int formState, String comment){
        PageData pageData = new PageData();
        pageData.put("status", String.valueOf(formState));
        pageData.put("id", Collections.singletonList(formId));
        try {
            bondsapplyDao.refer(pageData);
            saveMessage(formId, false, comment);
        } catch (Exception e) {
            log.error(ExceptionUtils.getStackTrace(e));
            throw new MyRuntimeException("回退失败");
        }
        return true;
    }

    /**
     * 单据完成后
     *
     * @param formId
     */
    public Boolean finish(String formId) {
        try {
            PageData pageData = new PageData();
            pageData.put("status", String.valueOf(FINISH));
            pageData.put("id", Collections.singletonList(formId));
            bondsapplyDao.refer(pageData);
            saveMessage(formId, true, "审批通过");
        } catch (Exception e) {
            LOGGER.error(ExceptionUtils.getStackTrace(e));
            throw new MyRuntimeException(new ObjResult("单据流转完成时修改单据状态失败！"));
        }
        return true;
    }

    private void saveMessage(String formId, boolean approval, String comment) throws Exception{
        PageData pd = bondsapplyDao.findbyid(formId);
        String dataSource = pd.getString("dataSource");
        String dataSourceId = pd.getString("dataSourceId");
        if (YthConstant.DATA_NINGBO.equals(dataSource)) {
            ApprovalMessage approvalMessage = new ApprovalMessage();
            approvalMessage.setDataSourceType(YthConstant.DATA_NINGBO);
            approvalMessage.setFilingUserName(UserCache.userIdToName(TokenUtil.getTokenUserId()));
            approvalMessage.setFormType(CQZC_BONDSAPPLY);
            approvalMessage.setDataSourceId(dataSourceId);
            approvalMessage.setOpinion(comment);
            approvalMessage.setIsApprove(approval ? 2 : 1);
            approvalMessage.setIsSend(1);
            approvalMessage.setFilingCode(pd.getString("code"));
            approvalMessageService.save(approvalMessage);
        }
    }

    public void goBackToStash(String formId) {
        try {
            PageData pageData = new PageData();
            pageData.put("status", String.valueOf(UNREVIEWED));
            pageData.put("id", Collections.singletonList(formId));
            bondsapplyDao.refer(pageData);
        } catch (Exception e) {
            LOGGER.error(ExceptionUtils.getStackTrace(e));
        }
    }


    /**
     * @param formIds 单据ids
     */
    public List<PageData> findByIds(List<String> formIds) {
        try {
            PageData pageData = new PageData();
            pageData.put("ids", formIds);
            return bondsapplyDao.findByIds(pageData);
        } catch (Exception e) {
            LOGGER.error(ExceptionUtils.getStackTrace(e));
            throw new MyRuntimeException(new ObjResult("根据单据id列表查询报表失败！"));
        }
    }

    @Override
    public List<Integer> getFormType() {
        return Arrays.asList(CQZC_BONDSAPPLY);
    }

    /**
     * 有效期内的列表
     *
     * @return
     */
    public ListResult<PageData> periodOfValidityList(PageData pd) {
        ListResult listResult;
        try {
            pd.put("orgId", TokenUtil.getTokenOrgId());
//            pd.put("effectiveDate", Tools.date2Str(new Date(),"yyyy-MM-dd"));
            pd.put("status", "3");
            Page page = new Page(false, pd);
            List<PageData> list = bondsapplyDao.listPage(page);

            PageData p1 = new PageData();
            p1.put("bondMaturity", Tools.date2Str(new Date(), "yyyy-MM-dd"));
            List<PageData> l1 = bondIssueDao.listIssuance(p1);

            //债券偿还
            List<PageData> repayment = new ArrayList<>();
            if (l1 != null && l1.size() > 0) {
                List<String> issueIds = l1.stream().map(issuePd -> issuePd.getString("id")).collect(Collectors.toList());
                if (CollectionUtil.isNotEmpty(issueIds)) {
                    pd.put("issueIds", issueIds);
                    repayment = bondsRepaymentDao.reportList(pd);
                }
            }

            listResult = new ListResult(CommonCode.SUCCESS);
            List<PageData> l = remainingIssuableAmount(list, l1, repayment);
            int PageNumber = pd.getInteger("pageNumber");
            int PageSize = pd.getInteger("pageSize");
            int pageTotal = (PageNumber - 1) * PageSize;
            int pageTotal1 = PageNumber * PageSize;
            if (l.size() < pageTotal1) {
                listResult.setList(l.subList(pageTotal, l.size()));
            } else {
                listResult.setList(l.subList(pageTotal, pageTotal1));
            }
            listResult.setTotal(l.size());
        } catch (Exception e) {
            LOGGER.error(ExceptionUtils.getStackTrace(e));
            listResult = new ListResult(CommonCode.ERROR);
        }
        return listResult;
    }

    /**
     * 剩余可发债额度
     *
     * @param list 申请列表
     * @param l    发行列表id
     * @param l1   偿还列表
     * @return
     */
    private List<PageData> remainingIssuableAmount(List<PageData> list, List<PageData> l, List<PageData> l1) throws Exception {
        List<PageData> list1 = new ArrayList<>();
        for (PageData p : list) {
            if (Tools.notEmpty(p.getString("bondVarieties"))) {
                BigDecimal sumIssuance = new BigDecimal("0");
                BigDecimal sumRepay = new BigDecimal("0");
                for (PageData p1 : l) {
                    if (p.getString("id").equals(p1.getString("bondsApplyId"))) {
                        if (Tools.notEmpty(p1.getString("issueAmount"))) {
                            sumIssuance = sumIssuance.add(new BigDecimal(p1.getString("issueAmount")));
                        }
                        if ("5".equals(p.getString("bondVarieties"))) {
                            for (PageData p2 : l1) {
                                if (p2.getString("bondIssueId").equals(p1.getString("id"))){
                                    String sumRepaymentOfPrincipal = p2.getString("repaymentOfPrincipal");
                                    if (Tools.notEmpty(sumRepaymentOfPrincipal)) {
                                        sumRepay = sumRepay.add(new BigDecimal(sumRepaymentOfPrincipal));
                                    }
                                }
                            }
                        }
                    }

                }
                //债券类型
                String quota = p.getString("quota");
                if (Tools.isEmpty(quota)) {
                    quota = "0";
                }
                BigDecimal b_quota = new BigDecimal(quota);
                BigDecimal remainingIssuableAmount = new BigDecimal("0");
                if ("5".equals(p.getString("bondVarieties"))) {
                    //剩余可发行额度
                    BigDecimal balance = b_quota.subtract(sumIssuance);
                    remainingIssuableAmount = sumRepay.add(balance);
                } else {
                    remainingIssuableAmount = b_quota.subtract(sumIssuance);
                }
                if (remainingIssuableAmount.floatValue() != 0) {
                    p.put("remainingIssuableAmount", remainingIssuableAmount.toPlainString());
                    list1.add(p);
                }
            }
        }
        return list1;
    }


    /**
     * 发行情况报告表
     *
     * @param pd
     * @return
     */
    public ListResult report(PageData pd) {
        try {
            ListResult listResult = new ListResult(CommonCode.SUCCESS);
            listResult.setList(reportProcessing(pd));
            return listResult;
        } catch (Exception e) {
            LOGGER.error(ExceptionUtils.getStackTrace(e));
            throw new MyRuntimeException();
        }
    }

    /**
     * 报表导出
     *
     * @param pd
     */
    public void reportExcel(PageData pd, HttpServletResponse response, OutputStream os) throws Exception {
        //导出数据源
//        String fname = "出资企业债券发行情况报告表";
        os = response.getOutputStream();//取得输出流
        response.reset();//清空输出流
        //下面是对中文文件名的处理
        response.setCharacterEncoding("UTF-8");//设置相应内容的编码格式
//        fname = java.net.URLEncoder.encode(fname, "UTF-8");
//        response.setHeader("Content-Disposition", "attachment;filename=" + new String(fname.getBytes("UTF-8"), "UTF-8") + ".xls");
        response.setContentType("application/msexcel");//定义输出类型
        HSSFWorkbook wb = new HSSFWorkbook();
        Sheet sheet = wb.createSheet();
        //合并第1行
        CellRangeAddress region = new CellRangeAddress(0, 0, 0, 21);// 下标从0开始 起始行号，终止行号， 起始列号，终止列号
        sheet.addMergedRegion(region);


        //标题1行
        //设置标题字体
        Font fontTitle = wb.createFont();
        fontTitle.setFontHeightInPoints((short) 16); //字体大小
        fontTitle.setFontName("宋体"); //字体
        fontTitle.setBold(true);
        //设置标题单元格类型
        CellStyle cellStyleTitle = wb.createCellStyle();
        //横向居中
        cellStyleTitle.setAlignment(HorizontalAlignment.CENTER);
        //垂直居中
        cellStyleTitle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyleTitle.setFont(fontTitle);
        String title1 = "出资企业债券发行情况报告表";
        //第1行
        Row row0 = sheet.createRow(0);
        //行高
        row0.setHeightInPoints((float) 24.9);
        //第1行第1列
        Cell cell01 = row0.createCell(0);
        cell01.setCellValue(title1);
        cell01.setCellStyle(cellStyleTitle);
        //第二行
        Row row2 = sheet.createRow(1);
        //汇总企业
        CellRangeAddress region2 = new CellRangeAddress(1, 2, 0, 0);// 下标从0开始 起始行号，终止行号， 起始列号，终止列号
        sheet.addMergedRegion(region2);
        //债券类型
        CellRangeAddress region3 = new CellRangeAddress(1, 2, 1, 1);// 下标从0开始 起始行号，终止行号， 起始列号，终止列号
        sheet.addMergedRegion(region3);
        //主管部门
        CellRangeAddress region3_1 = new CellRangeAddress(1, 2, 2, 2);// 下标从0开始 起始行号，终止行号， 起始列号，终止列号
        sheet.addMergedRegion(region3_1);
        //企业级次
        CellRangeAddress region4 = new CellRangeAddress(1, 2, 3, 3);// 下标从0开始 起始行号，终止行号， 起始列号，终止列号
        sheet.addMergedRegion(region4);
        //发行方
        CellRangeAddress region4_1 = new CellRangeAddress(1, 2, 4, 4);// 下标从0开始 起始行号，终止行号， 起始列号，终止列号
        sheet.addMergedRegion(region4_1);
        //额度情况
        CellRangeAddress region5 = new CellRangeAddress(1, 1, 5, 11);// 下标从0开始 起始行号，终止行号， 起始列号，终止列号
        sheet.addMergedRegion(region5);
        //发行情况
        CellRangeAddress region6 = new CellRangeAddress(1, 1, 12, 20);// 下标从0开始 起始行号，终止行号， 起始列号，终止列号
        sheet.addMergedRegion(region6);
        //备注
        CellRangeAddress region7 = new CellRangeAddress(1, 2, 21, 21);// 下标从0开始 起始行号，终止行号， 起始列号，终止列号
        sheet.addMergedRegion(region7);
        List<CellRangeAddress> l_re = new ArrayList<>();
        l_re.add(region2);
        l_re.add(region3);
        l_re.add(region3_1);
        l_re.add(region4);
        l_re.add(region4_1);
        l_re.add(region5);
        l_re.add(region6);
        l_re.add(region7);


        //一、二列宽度
        sheet.setColumnWidth((short) 0, (short) (252 * (24) + 323));
        sheet.setColumnWidth((short) 1, (short) (252 * (24) + 323));

        CellStyle cellStyleTitle2 = wb.createCellStyle();
        cellStyleTitle2.setWrapText(true);
        //拿到palette颜色板
        HSSFPalette customPalette = wb.getCustomPalette();
        //设置颜色
        customPalette.setColorAtIndex(IndexedColors.BLUE_GREY.index, (byte) 153, (byte) 204, (byte) 255);
        //横向居中
        cellStyleTitle2.setAlignment(HorizontalAlignment.CENTER);
        //垂直居中
        cellStyleTitle2.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyleTitle2.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex());
        cellStyleTitle2.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        //添加边框
        cellStyleTitle2.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyleTitle2.setBorderBottom(BorderStyle.THIN); //下边框  
        cellStyleTitle2.setBorderLeft(BorderStyle.THIN);//左边框  
        cellStyleTitle2.setBorderTop(BorderStyle.THIN);//上边框  
        cellStyleTitle2.setBorderRight(BorderStyle.THIN);

        Font fontTitle2 = wb.createFont();
        fontTitle2.setFontHeightInPoints((short) 9); //字体大小
        fontTitle2.setFontName("宋体"); //字体
        fontTitle2.setBold(true);
        cellStyleTitle2.setFont(fontTitle2);

        Cell cell2_00 = row2.createCell(0);
        cell2_00.setCellValue("汇总单位");
        cell2_00.setCellStyle(cellStyleTitle2);

        Cell cell2_01 = row2.createCell(1);
        cell2_01.setCellValue("债券类型");
        cell2_01.setCellStyle(cellStyleTitle2);


        Cell cell2_1 = row2.createCell(2);
        cell2_1.setCellValue("主管部门");
        cell2_1.setCellStyle(cellStyleTitle2);

        Cell cell2_1_1 = row2.createCell(3);
        cell2_1_1.setCellValue("企业级次");
        cell2_1_1.setCellStyle(cellStyleTitle2);

        Cell cell2_2 = row2.createCell(4);
        cell2_2.setCellValue("发行方");
        cell2_2.setCellStyle(cellStyleTitle2);

        Cell cell2_3 = row2.createCell(5);
        cell2_3.setCellValue("额度情况");
        cell2_3.setCellStyle(cellStyleTitle2);

        Cell cell2_10 = row2.createCell(12);
        cell2_10.setCellValue("发行情况");
        cell2_10.setCellStyle(cellStyleTitle2);

        Cell cell2_19 = row2.createCell(21);
        cell2_19.setCellValue("备注");
        cell2_19.setCellStyle(cellStyleTitle2);

        String[] cell_names = {
                "注册金额",
                "注册批准文号",
                "注册日期",
                "有效期",
                "有效截止日",
                "累计已发行金额",
                "剩余可发行额度",
                "发行债券名称",
                "主承销商",
                "联席承销商",
                "发行金额",
                "存续余额（已发行未偿还）",
                "起息日",
                "到期日",
                "期限",
                "利率",
        };
        //第三行
        Row row3 = sheet.createRow(2);
        for (int i = 0; i < cell_names.length; i++) {
            String cell_name = cell_names[i];
            Cell cell3_ = row3.createCell(i + 5);
            cell3_.setCellValue(cell_name);
            cell3_.setCellStyle(cellStyleTitle2);
        }

        //设置单元格宽度
        int[] titles_width = {
                19,
                19,
                14,
                9,
                11,
                11,
                11,
                11,
                11,
                11,
                11,
                11,
                11,
                11,
                11,
                11,
                11,
                11,
                11,
                11,
                34,
        };
        for (int i = 0; i < titles_width.length; i++) {
            sheet.setColumnWidth((short) i, (short) (252 * (titles_width[i]) + 323));
        }


        //内容
        //标题1行
        //设置标题字体
        Font fontTitle3 = wb.createFont();
        fontTitle3.setFontHeightInPoints((short) 11); //字体大小
        fontTitle3.setFontName("宋体"); //字体
        //设置标题单元格类型
        CellStyle cellStyleTitle3 = wb.createCellStyle();
        cellStyleTitle3.setFont(fontTitle3);
        //设置自动换行
        cellStyleTitle3.setWrapText(true);
        //横向居中
        cellStyleTitle3.setAlignment(HorizontalAlignment.CENTER);
        //垂直居中
        cellStyleTitle3.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyleTitle3.setBorderBottom(BorderStyle.THIN); //下边框  
        cellStyleTitle3.setBorderLeft(BorderStyle.THIN);//左边框  
        cellStyleTitle3.setBorderTop(BorderStyle.THIN);//上边框  
        cellStyleTitle3.setBorderRight(BorderStyle.THIN);//右边框 

        //处理数据
        List<PageData> list = reportProcessing(pd);
        //列key
        String[] keys = {
                "competentDepartment",//主管部门
                "enterpriseGrade",//企业级次
                "issuerName",//发行方
                "quota",//注册资金
                "registrationApprovalNumber",//注册审批文号
                "registrationDate",//注册日期
                "validityTerm",//有效期
                "effectiveDeadline",//有效截止日
                "sumIssue",//累计已发行金额
                "remainingIssuableAmount",//剩余可发行额度
        };
        //发行key
        String[] keys1 = {
                "name",//发行债券名称
                "leadUnderwriter",//主承销商
                "jointUnderwriter",//联席承销商
                "issueAmount",//发行金额
                "balance",//存续余额
                "bondValueDate",//起息日
                "bondMaturity",//到息日
                "bondTerm",//期限
                "couponRate",//利率
                "remarks",//备注
        };


        //记录的行号
        //第一层行数
        int number = 3;
        //循环集团
        for (PageData pageData : list) {
            String orgName = pageData.getString("orgName");
            if ("合计".equals(orgName)){
                Row row_c = sheet.createRow(number);
                Cell cell = row_c.createCell(0);
                cell.setCellValue(orgName);
                cell.setCellStyle(cellStyleTitle3);

                Cell cell5 = row_c.createCell(5);
                cell5.setCellValue(BigDecimalUtil.toDoubleValue((BigDecimal)pageData.get("sumQuota")));
                cell5.setCellStyle(cellStyleTitle3);

                Cell cell10 = row_c.createCell(10);
                cell10.setCellValue(BigDecimalUtil.toDoubleValue((BigDecimal)pageData.get("sumIssue")));
                cell10.setCellStyle(cellStyleTitle3);

                Cell cell11 = row_c.createCell(11);
                cell11.setCellValue(BigDecimalUtil.toDoubleValue((BigDecimal) pageData.get("sumRemainingIssuableAmount")));
                cell11.setCellStyle(cellStyleTitle3);

                Cell cell15 = row_c.createCell(15);
                cell15.setCellValue(BigDecimalUtil.toDoubleValue((BigDecimal) pageData.get("sumIssue")));
                cell15.setCellStyle(cellStyleTitle3);

                Cell cell16 = row_c.createCell(16);
                cell16.setCellValue(BigDecimalUtil.toDoubleValue((BigDecimal) pageData.get("sumBalance")));
                cell16.setCellStyle(cellStyleTitle3);
                for (int i = 1; i < 22; i++){
                    if(i != 5 && i != 10 && i != 11 && i != 15 && i != 16){
                        Cell c = row_c.createCell(i);
                        c.setCellStyle(cellStyleTitle3);
                    }
                }
                continue;
            }
            int sumRow = Integer.valueOf(pageData.getString("sumRow"));
            if (sumRow > 0) {
                //如果行数大于1，合并单元格
                if (sumRow > 1) {
                    //合并第一层集团
                    CellRangeAddress region8 = new CellRangeAddress(number, (number + sumRow - 1), 0, 0);// 下标从0开始 起始行号，终止行号， 起始列号，终止列号
                    sheet.addMergedRegion(region8);
                    l_re.add(region8);
                }
                //创建行
                Row row_c = sheet.createRow(number);
                Cell cell = row_c.createCell(0);
                cell.setCellValue(orgName);
                cell.setCellStyle(cellStyleTitle3);
                //第二层行数
                int number1 = number;
                //债券类型
                for (int a = 0; a <= 11; a++) {
                    String typeName = "";
                    if (a == 0) {
                        typeName = "企业债券";
                    }
                    if (a == 1) {
                        typeName = "公司债券";
                    }
                    if (a == 2) {
                        typeName = "中期票据";
                    }
                    if (a == 3) {
                        typeName = "短期融资券";
                    }
                    if (a == 4) {
                        typeName = "非定向公开债务融资工具";
                    }
                    if (a == 5) {
                        typeName = "超短期融资券";
                    }
                    if (a == 6) {
                        typeName = "永续中票";
                    }
                    if (a == 7) {
                        typeName = "其他";
                    }
                    if (a == 8) {
                        typeName = "非金融企业债券融资工具";
                    }
                    if (a == 9) {
                        typeName = "资产证券化";
                    }
                    if (a == 10) {
                        typeName = "保险资金";
                    }
                    if (a == 11) {
                        typeName = "保险资金投资计划";
                    }
                    if (a == 12) {
                        typeName = "可续期公司债券";
                    }
                    //拿到对应的债券类型
                    PageData type = (PageData) pageData.get("type" + (a + 1));
                    //对应债券类型的总行数
                    int sumRow1 = Integer.valueOf(type.getString("sumRow"));
                    if (sumRow1 > 0) {
                        //如果行数大于1，合并单元格
                        if (sumRow1 > 1) {
                            //合并债券类型
                            CellRangeAddress region9 = new CellRangeAddress(number1, (number1 + sumRow1)-1, 1, 1);// 下标从0开始 起始行号，终止行号， 起始列号，终止列号
                            sheet.addMergedRegion(region9);
                            l_re.add(region9);
                        }
                        Row row_c1 = sheet.getRow(number1);
                        if (row_c1 == null) {
                            row_c1 = sheet.createRow(number1);
                        }
                        Cell cell1 = row_c1.createCell(1);
                        cell1.setCellValue(typeName);
                        cell1.setCellStyle(cellStyleTitle3);

                        //额度情况
                        List<PageData> l = (List<PageData>) type.get("list");
                        //第三层行号
                        int number2 = number1;
                        //循环额度情况
                        for (PageData data : l) {
                            int issueRow = Integer.valueOf(data.getString("issueRow"));
                            Row row_c2 = sheet.getRow(number2);
                            if (row_c2 == null) {
                                row_c2 = sheet.createRow(number2);
                            }
                            //填写额度情况
                            for (int j = 0; j < keys.length; j++) {
                                //如果行数大于1，合并单元格
                                if (issueRow > 1) {
                                    //合并债券类型
                                    CellRangeAddress region9 = new CellRangeAddress(number2, (number2 + issueRow)-1, j + 2, j + 2);// 下标从0开始 起始行号，终止行号， 起始列号，终止列号
                                    sheet.addMergedRegion(region9);
                                    l_re.add(region9);
                                }

                                String key = keys[j];
                                // 注册日期
                                if ("registrationDate".equals(key) || "effectiveDeadline".equals(key)) {
                                    String registrationDate = data.getString(key);
                                    if (Tools.notEmpty(registrationDate)) {
                                        data.put(key, LocalDate.parse(registrationDate, DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
                                    }
                                }
                                Cell cell2 = row_c2.createCell(j + 2);
                                if ("quota".equals(key) || "sumIssue".equals(key) || "remainingIssuableAmount".equals(key)){
                                    cell2.setCellValue(BigDecimalUtil.toDoubleValue(BigDecimalUtil.toBigdecimal(data.getString(key))));
                                }else {
                                    cell2.setCellValue(data.getString(key));
                                }
                                cell2.setCellStyle(cellStyleTitle3);
                            }
                            //第四层行号
                            int number3 = number2;
                            //发行情况
                            List<PageData> issueList = (List<PageData>) data.get("issueList");
                            if(issueList!=null && issueList.size()>0){
                                for (PageData p1 : issueList) {
                                    Row row_c3 = sheet.getRow(number3);
                                    if (row_c3 == null) {
                                        row_c3 = sheet.createRow(number3);
                                    }
                                    for (int i = 0; i < keys1.length; i++) {
                                        String key = keys1[i];
                                        if ("bondValueDate".equals(key) || "bondMaturity".equals(key)) {
                                            String registrationDate = data.getString(key);
                                            if (Tools.notEmpty(registrationDate)) {
                                                data.put(key, LocalDate.parse(registrationDate,DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
                                            }
                                        }
                                        Cell cell2 = row_c3.createCell(i + 12);

                                        if ("issueAmount".equals(key) || "balance".equals(key)){
                                            cell2.setCellValue(BigDecimalUtil.toDoubleValue(BigDecimalUtil.toBigdecimal(p1.getString(key))));
                                        }else {
                                            cell2.setCellValue(p1.getString(key));
                                        }
                                        cell2.setCellStyle(cellStyleTitle3);
                                    }
                                    number3++;
                                }
                            }
                            number2 += issueRow;
                        }

                    }
                    number1+=sumRow1;
                }
                number += sumRow;
            }
        }
        for (CellRangeAddress cra : l_re) {
            //合并单元格设置边框
            RegionUtil.setBorderBottom(BorderStyle.THIN, cra, sheet); // 下边框
            RegionUtil.setBorderLeft(BorderStyle.THIN, cra, sheet); // 左边框
            RegionUtil.setBorderRight(BorderStyle.THIN, cra, sheet); // 有边框
            RegionUtil.setBorderTop(BorderStyle.THIN, cra, sheet); // 上边框
        }
        wb.write(os);
        os.flush();
        os.close();
        wb.close();
    }


    /**
     * 报表处理
     *
     * @param pd
     * @return
     */
    private List<PageData> reportProcessing(PageData pd) throws Exception {
        pd.put("status", "3");
//        pd.put("orgIds",pd.get("orgId"));
//        pd.remove("orgId");
        //债券申请
        Page page = new Page(false, pd);

        String endTimeStr = pd.getString("endTime");
        long endTime = com.plian.Tools.DateUtil.parse(com.plian.Tools.DateUtil.format(com.plian.Tools.DateUtil.now(),
                com.plian.Tools.DateUtil.PATTERN_DATE), com.plian.Tools.DateUtil.PATTERN_DATE).getTime();
        if (StringUtil.isNotBlank(endTimeStr)){
            endTime =  com.plian.Tools.DateUtil.parse(endTimeStr, DateUtil.PATTERN_DATE).getTime();
        }

        //根据所属集团查询数据
        List<PageData> apply = bondsapplyDao.reportListPage(page);
        List<String> bondsApplyIds = new ArrayList<>();
        for (PageData pageData : apply) {
            bondsApplyIds.add(pageData.getString("id"));
        }

        //债券发行
        List<PageData> issue = new ArrayList<>();
        List<String> issueIds = new ArrayList<>();
        if (bondsApplyIds != null && bondsApplyIds.size() > 0) {
            pd.put("bondsApplyIds", bondsApplyIds);
            issue = bondIssueDao.reportList(pd);
        }

        //债券偿还
        List<PageData> repayment = new ArrayList<>();
        if (issue != null && issue.size() > 0) {
            pd.remove("bondsApplyIds");
            for (PageData pageData : issue) {
                issueIds.add(pageData.getString("id"));
            }
            if (issueIds != null && issueIds.size() > 0) {
                pd.put("issueIds", issueIds);
                repayment = bondsRepaymentDao.reportList(pd);
            }
        }

        List<String> parentId = new ArrayList<>();
        parentId.add("001");
        PageData p_o = new PageData();
        p_o.put("parentId", parentId);
        List<Tree> baseInfoDaoTree = baseInfoDao.findTree(p_o);

        //根据集团分组
        //根据企业级次分组
        Map<String, List<PageData>> glist = apply.stream().
                filter(item -> Tools.notEmpty(item.getString("groupCompanyName"))).
                collect(Collectors.groupingBy(e -> e.getString("groupCompanyName")));

        List<PageData> list = new ArrayList<>();
        PageData sum = new PageData();
        sum.put("orgName", "合计");
        for (String s : glist.keySet()) {
            List<PageData> l=glist.get(s);

            PageData p = GroupreportProcessing(l, issue, repayment, baseInfoDaoTree, endTime, sum);
            if (p != null && p.size() > 0) {
                p.put("orgName", s);
            }
            list.add(p);
        }
        list.add(sum);
        return list;
    }


    private PageData GroupreportProcessing(
            List<PageData> apply,
            List<PageData> issue,
            List<PageData> repayment,
            List<Tree> baseInfoDaoTree,
            long endTime,
            PageData sum
    ) throws Exception {
        //企业债0
        List<PageData> type1 = new ArrayList<>();
        //公司债券1
        List<PageData> type2 = new ArrayList<>();
        //中期票据2
        List<PageData> type3 = new ArrayList<>();
        //短期融资券3
        List<PageData> type4 = new ArrayList<>();
        //非定向公开债务融资工具4
        List<PageData> type5 = new ArrayList<>();
        //超短期融资券5
        List<PageData> type6 = new ArrayList<>();
        //永续中票6
        List<PageData> type7 = new ArrayList<>();
        //其他7
        List<PageData> type8 = new ArrayList<>();
        //非金融企业债券融资工具8
        List<PageData> type9 = new ArrayList<>();
        //资产证券化9
        List<PageData> type10 = new ArrayList<>();
        //保险资金10
        List<PageData> type11 = new ArrayList<>();
        //保险资金投资计划11
        List<PageData> type12 = new ArrayList<>();
        //可续期公司债券12
        List<PageData> type13 = new ArrayList<>();

        //返回前端的对象
        PageData p1 = new PageData();
        PageData p2 = new PageData();
        PageData p3 = new PageData();
        PageData p4 = new PageData();
        PageData p5 = new PageData();
        PageData p6 = new PageData();
        PageData p7 = new PageData();
        PageData p8 = new PageData();
        PageData p9 = new PageData();
        PageData p10 = new PageData();
        PageData p11 = new PageData();
        PageData p12 = new PageData();
        PageData p13 = new PageData();

        //行数计算
        BigDecimal num1 = new BigDecimal("0");
        BigDecimal num2 = new BigDecimal("0");
        BigDecimal num3 = new BigDecimal("0");
        BigDecimal num4 = new BigDecimal("0");
        BigDecimal num5 = new BigDecimal("0");
        BigDecimal num6 = new BigDecimal("0");
        BigDecimal num7 = new BigDecimal("0");
        BigDecimal num8 = new BigDecimal("0");
        BigDecimal num9 = new BigDecimal("0");
        BigDecimal num10 = new BigDecimal("0");
        BigDecimal num11 = new BigDecimal("0");
        BigDecimal num12 = new BigDecimal("0");
        BigDecimal num13 = new BigDecimal("0");

        //汇总数据
        //注册金额
        BigDecimal sum_quota=new BigDecimal("0");
        //累计已发行金额
        BigDecimal sum_issue=new BigDecimal("0");
        //剩余可发行额度
        BigDecimal sum_remainingIssuableAmount=new BigDecimal("0");
        //存续余额
        BigDecimal sum_balance=new BigDecimal("0");

        //报表数据处理
        for (PageData a : apply) {
            //债券类型
            String type = a.getString("bondVarieties");
            if ("0".equals(type)) {
                PageData r = reportData(a, issue, repayment, baseInfoDaoTree, endTime);
                BigDecimal issueRow = new BigDecimal(r.getString("issueRow"));
                if (Integer.valueOf(issueRow.toPlainString()) == 0) {
                    num1 = num1.add(new BigDecimal("1"));
                } else {
                    num1 = num1.add(issueRow);
                }

                type1.add(r);
                //注册金额
                BigDecimal quot=new BigDecimal(r.getString("quota"));
                sum_quota=sum_quota.add(quot);
                //剩余可发行额度
                BigDecimal remainingIssuableAmount=new BigDecimal(r.getString("remainingIssuableAmount"));
                sum_remainingIssuableAmount=sum_remainingIssuableAmount.add(remainingIssuableAmount);
                //存续余额
                BigDecimal sumBalance=new BigDecimal(r.getString("sumBalance"));
                sum_balance=sum_balance.add(sumBalance);
                //累计已发行金额
                BigDecimal sumIssue=new BigDecimal(r.getString("sumIssue"));
                sum_issue=sum_issue.add(sumIssue);
            } else if ("1".equals(type)) {
                PageData r = reportData(a, issue, repayment, baseInfoDaoTree, endTime);
                BigDecimal issueRow = new BigDecimal(r.getString("issueRow"));
                if (Integer.valueOf(issueRow.toPlainString()) == 0) {
                    num2 = num2.add(new BigDecimal("1"));
                } else {
                    num2 = num2.add(issueRow);
                }

                type2.add(r);
                //注册金额
                BigDecimal quot=new BigDecimal(r.getString("quota"));
                sum_quota=sum_quota.add(quot);
                //剩余可发行额度
                BigDecimal remainingIssuableAmount=new BigDecimal(r.getString("remainingIssuableAmount"));
                sum_remainingIssuableAmount=sum_remainingIssuableAmount.add(remainingIssuableAmount);
                //存续余额
                BigDecimal sumBalance=new BigDecimal(r.getString("sumBalance"));
                sum_balance=sum_balance.add(sumBalance);
                //累计已发行金额
                BigDecimal sumIssue=new BigDecimal(r.getString("sumIssue"));
                sum_issue=sum_issue.add(sumIssue);
            } else if ("2".equals(type)) {
                PageData r = reportData(a, issue, repayment, baseInfoDaoTree, endTime);
                BigDecimal issueRow = new BigDecimal(r.getString("issueRow"));
                if (Integer.valueOf(issueRow.toPlainString()) == 0) {
                    num3 = num3.add(new BigDecimal("1"));
                } else {
                    num3 = num3.add(issueRow);
                }
                type3.add(r);
                if(Tools.notEmpty(r.getString("quota"))){
                    //注册金额
                    BigDecimal quot=new BigDecimal(r.getString("quota"));
                    sum_quota=sum_quota.add(quot);
                }
                //剩余可发行额度
                BigDecimal remainingIssuableAmount=new BigDecimal(r.getString("remainingIssuableAmount"));
                sum_remainingIssuableAmount=sum_remainingIssuableAmount.add(remainingIssuableAmount);
                //存续余额
                BigDecimal sumBalance=new BigDecimal(r.getString("sumBalance"));
                sum_balance=sum_balance.add(sumBalance);
                //累计已发行金额
                BigDecimal sumIssue=new BigDecimal(r.getString("sumIssue"));
                sum_issue=sum_issue.add(sumIssue);
            } else if ("3".equals(type)) {
                PageData r = reportData(a, issue, repayment, baseInfoDaoTree, endTime);
                BigDecimal issueRow = new BigDecimal(r.getString("issueRow"));
                if (Integer.valueOf(issueRow.toPlainString()) == 0) {
                    num4 = num4.add(new BigDecimal("1"));
                } else {
                    num4 = num4.add(issueRow);
                }
                type4.add(r);
                //注册金额
                BigDecimal quot=new BigDecimal(r.getString("quota"));
                sum_quota=sum_quota.add(quot);
                //剩余可发行额度
                BigDecimal remainingIssuableAmount=new BigDecimal(r.getString("remainingIssuableAmount"));
                sum_remainingIssuableAmount=sum_remainingIssuableAmount.add(remainingIssuableAmount);
                //存续余额
                BigDecimal sumBalance=new BigDecimal(r.getString("sumBalance"));
                sum_balance=sum_balance.add(sumBalance);
                //累计已发行金额
                BigDecimal sumIssue=new BigDecimal(r.getString("sumIssue"));
                sum_issue=sum_issue.add(sumIssue);
            } else if ("4".equals(type)) {
                PageData r = reportData(a, issue, repayment, baseInfoDaoTree, endTime);
                BigDecimal issueRow = new BigDecimal(r.getString("issueRow"));
                if (Integer.valueOf(issueRow.toPlainString()) == 0) {
                    num5 = num5.add(new BigDecimal("1"));
                } else {
                    num5 = num5.add(issueRow);
                }
                type5.add(r);
                //注册金额
                BigDecimal quot=new BigDecimal(r.getString("quota"));
                sum_quota=sum_quota.add(quot);
                //剩余可发行额度
                BigDecimal remainingIssuableAmount=new BigDecimal(r.getString("remainingIssuableAmount"));
                sum_remainingIssuableAmount=sum_remainingIssuableAmount.add(remainingIssuableAmount);
                //存续余额
                BigDecimal sumBalance=new BigDecimal(r.getString("sumBalance"));
                sum_balance=sum_balance.add(sumBalance);
                //累计已发行金额
                BigDecimal sumIssue=new BigDecimal(r.getString("sumIssue"));
                sum_issue=sum_issue.add(sumIssue);
            } else if ("5".equals(type)) {
                PageData r = reportData(a, issue, repayment, baseInfoDaoTree, endTime);
                BigDecimal issueRow = new BigDecimal(r.getString("issueRow"));
                if (Integer.valueOf(issueRow.toPlainString()) == 0) {
                    num6 = num6.add(new BigDecimal("1"));
                } else {
                    num6 = num6.add(issueRow);
                }
                type6.add(r);
                //注册金额
                BigDecimal quot=new BigDecimal(r.getString("quota"));
                sum_quota=sum_quota.add(quot);
                //剩余可发行额度
                BigDecimal remainingIssuableAmount=new BigDecimal(r.getString("remainingIssuableAmount"));
                sum_remainingIssuableAmount=sum_remainingIssuableAmount.add(remainingIssuableAmount);
                //存续余额
                BigDecimal sumBalance=new BigDecimal(r.getString("sumBalance"));
                sum_balance=sum_balance.add(sumBalance);
                //累计已发行金额
                BigDecimal sumIssue=new BigDecimal(r.getString("sumIssue"));
                sum_issue=sum_issue.add(sumIssue);
            } else if ("6".equals(type)) {
                PageData r = reportData(a, issue, repayment, baseInfoDaoTree, endTime);
                BigDecimal issueRow = new BigDecimal(r.getString("issueRow"));
                if (Integer.valueOf(issueRow.toPlainString()) == 0) {
                    num7 = num7.add(new BigDecimal("1"));
                } else {
                    num7 = num7.add(issueRow);
                }
                type7.add(r);
                //注册金额
                BigDecimal quot=new BigDecimal(r.getString("quota"));
                sum_quota=sum_quota.add(quot);
                //剩余可发行额度
                BigDecimal remainingIssuableAmount=new BigDecimal(r.getString("remainingIssuableAmount"));
                sum_remainingIssuableAmount=sum_remainingIssuableAmount.add(remainingIssuableAmount);
                //存续余额
                BigDecimal sumBalance=new BigDecimal(r.getString("sumBalance"));
                sum_balance=sum_balance.add(sumBalance);
                //累计已发行金额
                BigDecimal sumIssue=new BigDecimal(r.getString("sumIssue"));
                sum_issue=sum_issue.add(sumIssue);
            } else if ("7".equals(type)) {
                PageData r = reportData(a, issue, repayment, baseInfoDaoTree, endTime);
                BigDecimal issueRow = new BigDecimal(r.getString("issueRow"));
                if (Integer.valueOf(issueRow.toPlainString()) == 0) {
                    num8 = num8.add(new BigDecimal("1"));
                } else {
                    num8 = num8.add(issueRow);
                }
                type8.add(r);
                //注册金额
                BigDecimal quot=new BigDecimal(r.getString("quota"));
                sum_quota=sum_quota.add(quot);
                //剩余可发行额度
                BigDecimal remainingIssuableAmount=new BigDecimal(r.getString("remainingIssuableAmount"));
                sum_remainingIssuableAmount=sum_remainingIssuableAmount.add(remainingIssuableAmount);
                //存续余额
                BigDecimal sumBalance=new BigDecimal(r.getString("sumBalance"));
                sum_balance=sum_balance.add(sumBalance);
                //累计已发行金额
                BigDecimal sumIssue=new BigDecimal(r.getString("sumIssue"));
                sum_issue=sum_issue.add(sumIssue);
            } else if ("8".equals(type)) {
                PageData r = reportData(a, issue, repayment, baseInfoDaoTree, endTime);
                BigDecimal issueRow = new BigDecimal(r.getString("issueRow"));
                if (Integer.valueOf(issueRow.toPlainString()) == 0) {
                    num9 = num9.add(new BigDecimal("1"));
                } else {
                    num9 = num9.add(issueRow);
                }
                type9.add(r);
                //注册金额
                BigDecimal quot=new BigDecimal(r.getString("quota"));
                sum_quota=sum_quota.add(quot);
                //剩余可发行额度
                BigDecimal remainingIssuableAmount=new BigDecimal(r.getString("remainingIssuableAmount"));
                sum_remainingIssuableAmount=sum_remainingIssuableAmount.add(remainingIssuableAmount);
                //存续余额
                BigDecimal sumBalance=new BigDecimal(r.getString("sumBalance"));
                sum_balance=sum_balance.add(sumBalance);
                //累计已发行金额
                BigDecimal sumIssue=new BigDecimal(r.getString("sumIssue"));
                sum_issue=sum_issue.add(sumIssue);
            } else if ("9".equals(type)) {
                PageData r = reportData(a, issue, repayment, baseInfoDaoTree, endTime);
                BigDecimal issueRow = new BigDecimal(r.getString("issueRow"));
                if (Integer.valueOf(issueRow.toPlainString()) == 0) {
                    num10 = num10.add(new BigDecimal("1"));
                } else {
                    num10 = num10.add(issueRow);
                }
                type10.add(r);
//                //注册金额
                BigDecimal quot=new BigDecimal(r.getString("quota"));
                sum_quota=sum_quota.add(quot);
                //剩余可发行额度
                BigDecimal remainingIssuableAmount=new BigDecimal(r.getString("remainingIssuableAmount"));
                sum_remainingIssuableAmount=sum_remainingIssuableAmount.add(remainingIssuableAmount);
                //存续余额
                BigDecimal sumBalance=new BigDecimal(r.getString("sumBalance"));
                sum_balance=sum_balance.add(sumBalance);
                //累计已发行金额
                BigDecimal sumIssue=new BigDecimal(r.getString("sumIssue"));
                sum_issue=sum_issue.add(sumIssue);
            } else if ("10".equals(type)) {
                PageData r = reportData(a, issue, repayment, baseInfoDaoTree, endTime);
                BigDecimal issueRow = new BigDecimal(r.getString("issueRow"));
                if (Integer.valueOf(issueRow.toPlainString()) == 0) {
                    num11 = num11.add(new BigDecimal("1"));
                } else {
                    num11 = num11.add(issueRow);
                }
                type11.add(r);
//                //注册金额
                BigDecimal quot=new BigDecimal(r.getString("quota"));
                sum_quota=sum_quota.add(quot);
                //剩余可发行额度
                BigDecimal remainingIssuableAmount=new BigDecimal(r.getString("remainingIssuableAmount"));
                sum_remainingIssuableAmount=sum_remainingIssuableAmount.add(remainingIssuableAmount);
                //存续余额
                BigDecimal sumBalance=new BigDecimal(r.getString("sumBalance"));
                sum_balance=sum_balance.add(sumBalance);
                //累计已发行金额
                BigDecimal sumIssue=new BigDecimal(r.getString("sumIssue"));
                sum_issue=sum_issue.add(sumIssue);
            } else if ("11".equals(type)) {
                PageData r = reportData(a, issue, repayment, baseInfoDaoTree, endTime);
                BigDecimal issueRow = new BigDecimal(r.getString("issueRow"));
                if (Integer.valueOf(issueRow.toPlainString()) == 0) {
                    num12 = num12.add(new BigDecimal("1"));
                } else {
                    num12 = num12.add(issueRow);
                }
                type12.add(r);
                //注册金额
                BigDecimal quot=new BigDecimal(r.getString("quota"));
                sum_quota=sum_quota.add(quot);
                //剩余可发行额度
                BigDecimal remainingIssuableAmount=new BigDecimal(r.getString("remainingIssuableAmount"));
                sum_remainingIssuableAmount=sum_remainingIssuableAmount.add(remainingIssuableAmount);
                //存续余额
                BigDecimal sumBalance=new BigDecimal(r.getString("sumBalance"));
                sum_balance=sum_balance.add(sumBalance);
                //累计已发行金额
                BigDecimal sumIssue=new BigDecimal(r.getString("sumIssue"));
                sum_issue=sum_issue.add(sumIssue);
            }  else if ("12".equals(type)) {
                PageData r = reportData(a, issue, repayment, baseInfoDaoTree, endTime);
                BigDecimal issueRow = new BigDecimal(r.getString("issueRow"));
                if (Integer.valueOf(issueRow.toPlainString()) == 0) {
                    num13 = num13.add(new BigDecimal("1"));
                } else {
                    num13 = num13.add(issueRow);
                }
                type13.add(r);
                //注册金额
                BigDecimal quot=new BigDecimal(r.getString("quota"));
                sum_quota=sum_quota.add(quot);
                //剩余可发行额度
                BigDecimal remainingIssuableAmount=new BigDecimal(r.getString("remainingIssuableAmount"));
                sum_remainingIssuableAmount=sum_remainingIssuableAmount.add(remainingIssuableAmount);
                //存续余额
                BigDecimal sumBalance=new BigDecimal(r.getString("sumBalance"));
                sum_balance=sum_balance.add(sumBalance);
                //累计已发行金额
                BigDecimal sumIssue=new BigDecimal(r.getString("sumIssue"));
                sum_issue=sum_issue.add(sumIssue);
            }else {
                throw new MyRuntimeException("未知的债券类型!!!");
            }
        }
        //返回结果
        PageData re = new PageData();
        p1.put("sumRow", num1.toPlainString());
        p1.put("list", type1);
        re.put("type1", p1);

        p2.put("sumRow", num2.toPlainString());
        p2.put("list", type2);
        re.put("type2", p2);

        p3.put("sumRow", num3.toPlainString());
        p3.put("list", type3);
        re.put("type3", p3);

        p4.put("sumRow", num4.toPlainString());
        p4.put("list", type4);
        re.put("type4", p4);

        p5.put("sumRow", num5.toPlainString());
        p5.put("list", type5);
        re.put("type5", p5);

        p6.put("sumRow", num6.toPlainString());
        p6.put("list", type6);
        re.put("type6", p6);

        p7.put("sumRow", num7.toPlainString());
        p7.put("list", type7);
        re.put("type7", p7);

        p8.put("sumRow", num8.toPlainString());
        p8.put("list", type8);
        re.put("type8", p8);

        p9.put("sumRow", num9.toPlainString());
        p9.put("list", type9);
        re.put("type9", p9);

        p10.put("sumRow", num10.toPlainString());
        p10.put("list", type10);
        re.put("type10", p10);

        p11.put("sumRow", num11.toPlainString());
        p11.put("list", type11);
        re.put("type11", p11);

        p12.put("sumRow", num12.toPlainString());
        p12.put("list", type12);
        re.put("type12", p12);

        p13.put("sumRow", num13.toPlainString());
        p13.put("list", type13);
        re.put("type13", p13);

        re.put("sumRow", num1.add(num2).add(num3).add(num4).add(num5).add(num6).add(num7)
                .add(num8).add(num9).add(num10).add(num11).add(num12).add(num13).toPlainString());
        //汇总
        //存续余额
        sum.put("sumBalance", BigDecimalUtil.add(sum_balance, (BigDecimal)sum.get("sumBalance")));
        //剩余可发行额度
        sum.put("sumRemainingIssuableAmount", BigDecimalUtil.add(sum_remainingIssuableAmount, (BigDecimal) sum.get("sumRemainingIssuableAmount")));
        //注册金额
        sum.put("sumQuota", BigDecimalUtil.add(sum_quota, (BigDecimal) sum.get("sumQuota")));
        //发行金额
        sum.put("issueAmount",BigDecimalUtil.add(sum_issue, (BigDecimal) sum.get("issueAmount")));
        //累计已发行金额
        sum.put("sumIssue",BigDecimalUtil.add(sum_issue, (BigDecimal) sum.get("sumIssue")));


        //单位名称
//            PageData org=orgzationDao.findbyid(pd.getString("orgId"));
//            re.put("orgName",org.getString("name"));
        return re;
    }


    /**
     * @param a         债券申请单条
     * @param issue     债券发行集合
     * @param repayment 债券偿还集合
     * @return
     */
    private PageData reportData(PageData a, List<PageData> issue, List<PageData> repayment, List<Tree> baseInfoDaoTree, long endTime) throws Exception {
        DateTimeFormatter fmt = DateTimeFormatter.ofPattern("yyyy-MM-dd hh:mm:ss");
        PageData p = new PageData();
        //主管部门
        p.put("competentDepartment", a.getString("competentDepartment"));

        //企业级次
        String enterpriseGrade = a.getString("enterpriseGrade");
        for (Tree tree : baseInfoDaoTree) {
            if (tree.getCode().equals(enterpriseGrade)) {
                enterpriseGrade = tree.getName();
                break;
            }
        }
        p.put("enterpriseGrade", enterpriseGrade);
        //发行方
        p.put("issuerName", a.getString("issuerName"));
        //注册金额
        p.put("quota", a.getString("quota"));
        //注册批准文号
        p.put("registrationApprovalNumber", a.getString("registrationApprovalNumber"));
        //有效截止日期
        p.put("effectiveDeadline", a.getString("effectiveDeadline"));
        //注册日期
        String registration_date = a.getString("registrationDate");
        p.put("registrationDate", registration_date);

        //有效截止日期
        String effective_deadline = a.getString("effectiveDeadline");
        //有效期计算
        if (Tools.notEmpty(registration_date) && Tools.notEmpty(effective_deadline)) {
            LocalDate d1 = LocalDate.parse(registration_date, fmt);
            LocalDate d2 = LocalDate.parse(effective_deadline, fmt);
            p.put("validityTerm", (d2.toEpochDay() - d1.toEpochDay()) + "天");
//            Period period = Period.between(d1, d2);
//            p.put("validityTerm",period.getYears()+"年"+period.getMonths()+"月"+period.getDays()+"日");
        } else {
            p.put("validityTerm", "0");
        }
        //同期发行的债券
        List<PageData> l = new ArrayList<>();
        //累计发行金额
        BigDecimal sum_issue = new BigDecimal("0");
        //如果为超短期时,需要计算的累计偿还额度
        BigDecimal sum_issue1 = new BigDecimal("0");
        //存续余额汇总
        BigDecimal sum_balance = new BigDecimal("0");

        //发行行数
        int issueRow = 0;
        //累计已发行金额
        for (PageData i : issue) {
            //同期发行
            if (i.getString("bondsApplyId").equals(a.getString("id"))) {
                if (Tools.notEmpty(i.getString("issueAmount"))) {
                    BigDecimal issueAmount = new BigDecimal(i.getString("issueAmount"));
                    sum_issue = sum_issue.add(issueAmount);
                }
                //发行情况
                PageData p1 = new PageData();
                //发行债券名称
                p1.put("name", i.getString("name"));
                //主承销商
                p1.put("leadUnderwriter", i.getString("leadUnderwriter"));
                //联席承销商
                p1.put("jointUnderwriter", i.getString("jointUnderwriter"));
                //发行金额
                p1.put("issueAmount", i.getString("issueAmount"));
                //起息日
                String bondValueDate = i.getString("bondValueDate");
                if (Tools.notEmpty(bondValueDate)) {
                    bondValueDate = Tools.date2Str(Tools.str2Date(bondValueDate), "yyyy-MM-dd");
                }
                p1.put("bondValueDate", bondValueDate);
                //到期日
                String bondMaturity = i.getString("bondMaturity");
                if (Tools.notEmpty(bondMaturity)) {
                    bondMaturity = Tools.date2Str(Tools.str2Date(bondMaturity), "yyyy-MM-dd");
                }
                p1.put("bondMaturity", bondMaturity);
                //期限
                p1.put("bondTerm", i.getString("bondTerm"));
                //利率
                p1.put("couponRate", i.getString("couponRate"));
                //备注
                p1.put("remarks", i.getString("remarks"));
                //存续余额计算
                //已偿还金额
                BigDecimal sumRepay = new BigDecimal("0");
                //计算已偿还金额
                for (PageData r : repayment) {
                    if (r.getString("bondIssueId").equals(i.getString("id"))) {
                        if (Tools.notEmpty(r.getString("repaymentOfPrincipal"))) {
                            BigDecimal repaymentOfPrincipal = new BigDecimal(r.getString("repaymentOfPrincipal"));
                            sumRepay = sumRepay.add(repaymentOfPrincipal);
                        }
                    }
                }
                String issueAmount1 = i.getString("issueAmount");
                if (Tools.isEmpty(issueAmount1)) {
                    issueAmount1 = "0";
                }
                //发行金额
                BigDecimal issueAmount = new BigDecimal(issueAmount1);
                //发行-已偿还累计
                BigDecimal balance = issueAmount.subtract(sumRepay);
                p1.put("balance", balance.toPlainString());
                //超短期金额情况
                if ("5".equals(a.getString("bondVarieties"))) {
                    //剩余可发行额度
                    sum_issue1 = sum_issue1.add(sumRepay);
                }
                l.add(p1);
                sum_balance = sum_balance.add(balance);
            }
        }
        if (l == null || l.size() <= 0) {
            PageData p1 = new PageData();
            p1.put("balance", null);
            p1.put("bondMaturity", null);
            p1.put("bondTerm", null);
            p1.put("bondValueDate", null);
            p1.put("couponRate", null);
            p1.put("issueAmount", null);
            p1.put("jointUnderwriter", null);
            p1.put("leadUnderwriter", null);
            p1.put("name", null);
            p1.put("remarks", null);
            l.add(p1);
        }
        p.put("issueList", l);
        //累计发行金额
        p.put("sumIssue", sum_issue.toPlainString());
        //剩余可发行额度
        //注册金额
        String quota = a.getString("quota");
        if (Tools.isEmpty(quota)) {
            quota = "0";
        }
        BigDecimal b_quota = new BigDecimal(quota);
        //剩余可发行额度
        if ("5".equals(a.getString("bondVarieties"))) {
            //剩余可发行额度
            p.put("remainingIssuableAmount", b_quota.subtract(sum_issue).add(sum_issue1).toPlainString());
        } else {
            p.put("remainingIssuableAmount", b_quota.subtract(sum_issue).toPlainString());
        }

        String effectiveDeadline = a.getString("effectiveDeadline");
        if(StringUtil.isNotBlank(effectiveDeadline)){
            long deadLine = DateUtil.parse(effectiveDeadline, DateUtil.PATTERN_DATETIME).getTime();
            if(deadLine < endTime){
                p.put("remainingIssuableAmount", new BigDecimal(0));
            }
        }
        //发行行数
        p.put("issueRow", l.size());
        //发行的存续余额汇总
        p.put("sumBalance", sum_balance);
        return p;
    }

    public ListResult reportList(PageData pd) throws Exception {
        ListResult listResult;
        try {
            Page page = new Page(false, pd);
            List<PageData> list = bondsapplyDao.reportListPage(page);
            listResult = new ListResult(CommonCode.SUCCESS);
            listResult.setList(list);
            listResult.setTotal(page.getTotal());
        } catch (Exception e) {
            LOGGER.error(ExceptionUtils.getStackTrace(e));
            listResult = new ListResult(CommonCode.ERROR);
        }
        return listResult;
    }

//    public String ythSubmitBatch(String data){
//        YthResult ythResult = new YthResult();
//        //成功的id集合
//        List<String> successIds = new ArrayList<>();
//        //失败的id集合
//        List<String> failIds = new ArrayList<>();
//        //失败的原因
//        List<String> failReasons = new ArrayList<>();
//        //将json字符串转换成list
//        List<BondsApply> dataList = JSON.parseArray(data, BondsApply.class);
//        if (CollectionUtil.isEmpty(dataList)){
//            failReasons.add("数据为空");
//            ythResult.setFailReason(StringUtils.join(failReasons, StringPool.COMMA));
//            return JSON.toJSONString(ythResult);
//        }
//        List<BondsApply> oldList = list(Wrappers.<BondsApply>lambdaQuery()
//                .eq(BondsApply::getDataSource, YthConstant.DATA_NINGBO));
//        Map<String, BondsApply> oldMap = oldList.stream().collect(Collectors.toMap(BondsApply::getDataSourceId, Function.identity()));
//        List<String> cantSubmitStatus = Stream.of(String.valueOf(FINISH), String.valueOf(FLOW)).collect(Collectors.toList());
//        List<String> cantSubmitList = oldList.stream().filter(item -> cantSubmitStatus.contains(item.getStatus()))
//                .map(BondsApply::getId).collect(Collectors.toList());
//        List<Org> orgList = orgService.getYthOrgList();
//        Map<String, Org> dataSourceIdOrgMap = orgList.stream().collect(Collectors.toMap(Org::getDataSourceId, Function.identity()));
//        Date now = DateUtil.now();
//        List<BondsApply> submitList = new ArrayList<>();
//        for (BondsApply bondsApply : dataList){
//            if (cantSubmitList.contains(bondsApply.getDataSourceId())){
//                failIds.add(bondsApply.getDataSourceId());
//                failReasons.add(bondsApply.getDataSourceId() + "该数据已提交");
//                continue;
//            }
//            successIds.add(bondsApply.getDataSourceId());
//            if (oldMap.containsKey(bondsApply.getDataSourceId())){
//                bondsApply.setId(oldMap.get(bondsApply.getDataSourceId()).getId());
//                bondsApply.setUpdateTime(now);
//            }else {
//                bondsApply.setCode(formCodeService.getCode(FormTypeConstant.CQZC_BONDSAPPLY));
//                bondsApply.setCreateDate(now);
//            }
//            bondsApply.setFormType(String.valueOf(FormTypeConstant.CQZC_BONDSAPPLY));
//            bondsApply.setDataSource(YthConstant.DATA_NINGBO);
//            if (StringUtil.isNotBlank(bondsApply.getIssuer()) && dataSourceIdOrgMap.containsKey(bondsApply.getIssuer())){
//                bondsApply.setIssuer(dataSourceIdOrgMap.get(bondsApply.getIssuer()).getId());
//            }
//            if (StringUtil.isNotBlank(bondsApply.getOrgId()) && dataSourceIdOrgMap.containsKey(bondsApply.getOrgId())){
//                bondsApply.setOrgId(dataSourceIdOrgMap.get(bondsApply.getOrgId()).getId());
//                bondsApply.setCreateCompanyId(bondsApply.getOrgId());
//            }
//            if (StringUtil.isNotBlank(bondsApply.getDecisionMakingBody()) && dataSourceIdOrgMap.containsKey(bondsApply.getDecisionMakingBody())){
//                bondsApply.setDecisionMakingBody(dataSourceIdOrgMap.get(bondsApply.getDecisionMakingBody()).getId());
//            }
//            if (StringUtil.isNotBlank(bondsApply.getGroupCompany()) && dataSourceIdOrgMap.containsKey(bondsApply.getGroupCompany())){
//                bondsApply.setGroupCompany(dataSourceIdOrgMap.get(bondsApply.getGroupCompany()).getId());
//            }
//            bondsApply.setStatus(String.valueOf(UNREVIEWED));
//            submitList.add(bondsApply);
//        }
//
//        if (CollectionUtil.isNotEmpty(submitList)){
//            saveOrUpdateBatch(submitList);
//            for (BondsApply bondsApply : submitList){
//                fileService.upYthFile(bondsApply.getAttachIds(), bondsApply.getId());
//            }
//        }
//
//        ythResult.setSuccessIds(successIds);
//        ythResult.setFailIds(failIds);
//        ythResult.setFailReason(StringUtils.join(failReasons, StringPool.COMMA));
//        //ythResult转换成json字符串
//        return JSON.toJSONString(ythResult);
//
//    }
}